package ooboo;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import twitter4j.Status;
import twitter4j.http.AccessToken;
import twitter4j.org.json.JSONStringer;

public class Storage
{
	private static final short DATA_UNREAD = 0x1;
	
	private static Connection con;
	private static PreparedStatement addaccountStmt;
	private static PreparedStatement getNamesStmt;
	private static PreparedStatement getAccessStmt;
	
	private static Map<String,PreparedStatement> getMaxIDStmts;
	private static Map<String,PreparedStatement> getRepliesByRootStmts;
	private static Map<String,PreparedStatement> getReplyByIDStmts;
	private static Map<String,PreparedStatement> getThreadByIDStmts;
	private static Map<String,PreparedStatement> getThreadsStmts;
	private static Map<String,PreparedStatement> lastUpdatedStmts;
	private static Map<String,PreparedStatement> storeReplyStmts;
	private static Map<String,PreparedStatement> storeThreadStmts;
	
	public static void connect(boolean create)
	{
		try
		{
			String url = "jdbc:derby:storage";
			if(create)
				url += ";create=true";
			
			System.setProperty("derby.system.home",Assets.getAppDataFolder().getPath());
			
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
			con = DriverManager.getConnection(url);
			
			if(create)
			{
				Statement createSchema = con.createStatement();
				createSchema.execute("CREATE SCHEMA OOBOO");
				
				Statement stmt = con.createStatement();
				stmt.execute("CREATE TABLE OOBOO.Accounts(" +
						"screen_name VARCHAR(16) PRIMARY KEY," +
						"accessToken_class BLOB NOT NULL)");
				stmt.close();
			}
			
			addaccountStmt = con.prepareStatement("INSERT INTO OOBOO.Accounts VALUES(?,?)");
			getNamesStmt = con.prepareStatement("SELECT screen_name FROM OOBOO.Accounts");
			getAccessStmt = con.prepareStatement("SELECT accessToken_class FROM OOBOO.Accounts WHERE screen_name = ?");
			
			getMaxIDStmts = new Hashtable<String,PreparedStatement>();
			getRepliesByRootStmts = new Hashtable<String,PreparedStatement>();
			getReplyByIDStmts = new Hashtable<String,PreparedStatement>();
			getThreadByIDStmts = new Hashtable<String,PreparedStatement>();
			getThreadsStmts = new Hashtable<String,PreparedStatement>();
			lastUpdatedStmts = new Hashtable<String,PreparedStatement>();
			storeReplyStmts = new Hashtable<String,PreparedStatement>();
			storeThreadStmts = new Hashtable<String,PreparedStatement>();
			
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT screen_name FROM OOBOO.Accounts");
			
			while(rs.next())
			{
				String screenName = rs.getString(1);
				getMaxIDStmts.put(screenName,con.prepareStatement("SELECT max(t.id),max(r.id) FROM " + screenName + 
						".Tweets as t, " + screenName +".Replies as r"));
				getRepliesByRootStmts.put(screenName,con.prepareStatement("SELECT * FROM " + screenName + 
						".Replies WHERE root = ?"));
				getReplyByIDStmts.put(screenName,con.prepareStatement("SELECT * FROM " + screenName + 
						".Replies WHERE id = ?"));	
				getThreadByIDStmts.put(screenName,con.prepareStatement("SELECT * FROM " + screenName + 
						".Tweets WHERE id = ?"));
				getThreadsStmts.put(screenName,con.prepareStatement("SELECT * FROM " + screenName + 
						".Tweets ORDER BY last_updated DESC"));
				lastUpdatedStmts.put(screenName,con.prepareStatement("UPDATE " + screenName + 
						".Tweets SET last_updated = ? WHERE id = ?"));
				storeReplyStmts.put(screenName,con.prepareStatement("INSERT INTO " + screenName + 
						".Replies VALUES(?,?,?,?,?,?)"));
				storeThreadStmts.put(screenName,con.prepareStatement("INSERT INTO " + screenName + 
						".Tweets VALUES(?,?,?,?,?,?,?)"));
			}
			rs.close();
			stmt.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	public static void addAccount(String screenName,AccessToken accessToken)
	{
		try
		{
			ByteArrayOutputStream accessTokenBytes = new ByteArrayOutputStream();
			ObjectOutputStream output = new ObjectOutputStream(accessTokenBytes);
			output.writeObject(accessToken);
			output.close();
			
			addaccountStmt.setString(1,screenName);
			addaccountStmt.setBytes(2,accessTokenBytes.toByteArray());
			addaccountStmt.executeUpdate();
			
			Statement stmt = con.createStatement();
			stmt.execute("CREATE SCHEMA " + screenName);
			stmt.close();
			
			stmt = con.createStatement();
			stmt.execute("CREATE TABLE " + screenName + ".Tweets(" +
					"id BIGINT PRIMARY KEY," +
					"created_at TIMESTAMP NOT NULL," +
					"text VARCHAR(140) NOT NULL," +
					"source VARCHAR(64) NOT NULL," +
					"screen_name VARCHAR(16) NOT NULL," +
					"data SMALLINT NOT NULL," +
					"last_updated TIMESTAMP NOT NULL)");
			stmt.close();
			
			stmt = con.createStatement();
			stmt.execute("CREATE TABLE " + screenName + ".Replies(" +
					"id BIGINT PRIMARY KEY," +
					"created_at TIMESTAMP NOT NULL," +
					"text VARCHAR(140) NOT NULL," +
					"source VARCHAR(64) NOT NULL," +
					"screen_name VARCHAR(16) NOT NULL," +
					"root BIGINT NOT NULL," +
					"CONSTRAINT root_key FOREIGN KEY (root) REFERENCES " +
					screenName + ".Tweets ON DELETE CASCADE)");
			stmt.close();
			
			getMaxIDStmts.put(screenName,con.prepareStatement("SELECT max(ID) FROM " + screenName + 
					".Tweets"));
			getRepliesByRootStmts.put(screenName,con.prepareStatement("SELECT * FROM " + screenName + 
					".Replies WHERE root = ?"));
			getReplyByIDStmts.put(screenName,con.prepareStatement("SELECT * FROM " + screenName + 
					".Replies WHERE id = ?"));
			getThreadByIDStmts.put(screenName,con.prepareStatement("SELECT * FROM " + screenName + 
					".Tweets WHERE id = ?"));
			getThreadsStmts.put(screenName,con.prepareStatement("SELECT * FROM " + screenName + 
					".Tweets ORDER BY last_updated DESC"));
			lastUpdatedStmts.put(screenName,con.prepareStatement("UPDATE " + screenName + 
					".Tweets SET last_updated = ? WHERE id = ?"));
			storeReplyStmts.put(screenName,con.prepareStatement("INSERT INTO " + screenName + 
					".Replies VALUES(?,?,?,?,?,?)"));
			storeThreadStmts.put(screenName,con.prepareStatement("INSERT INTO " + screenName + 
					".Tweets VALUES(?,?,?,?,?,?,?)"));
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	public static long getMaxID (String screenName) {
		long ID = 0;
		
		try
		{
			ResultSet rs = getMaxIDStmts.get(screenName).executeQuery();
			if(rs.next())
			{
				ID = Math.max(rs.getLong(1),rs.getLong(2));
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return ID;
	}
	
	public static String[] getAllScreenNames()
	{
		Vector<String> ret = new Vector<String>();
		try
		{
			ResultSet rs = getNamesStmt.executeQuery();
			while(rs.next())
				ret.add(rs.getString(1));
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return ret.toArray(new String[ret.size()]);
	}
	
	public static AccessToken getAccessToken(String screenName)
	{
		AccessToken accessToken = null;
		try
		{
			getAccessStmt.setString(1,screenName);
			ResultSet rs = getAccessStmt.executeQuery();
			if(rs.next())
			{
				byte[] bytes = rs.getBytes(1);
				if(bytes != null)
				{
					ObjectInputStream input = new ObjectInputStream(new ByteArrayInputStream(bytes));
					accessToken = (AccessToken)input.readObject();
				}
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return accessToken;
	}
	
	public static TweetThread[] getThreads(String screenName,int count)
	{
		List<TweetThread> threads = new ArrayList<TweetThread>();
		try
		{
			PreparedStatement getRepliesStmt = getRepliesByRootStmts.get(screenName);
			ResultSet rsThread = getThreadsStmts.get(screenName).executeQuery();
			while(rsThread.next() && threads.size() < count)
			{
				List<StoredTweet> replies = new LinkedList<StoredTweet>();
				
				getRepliesStmt.setLong(1,rsThread.getLong(1));
				ResultSet rsReplies = getRepliesStmt.executeQuery();
				while(rsReplies.next())
					replies.add(new StoredTweet(rsReplies.getLong(1),rsReplies.getTimestamp(2),
							rsReplies.getString(3),rsReplies.getString(4),rsReplies.getString(5)));
				rsReplies.close();
				
				threads.add(new TweetThread(
						new StoredTweet(rsThread.getLong(1),rsThread.getTimestamp(2),rsThread.getString(3),
								rsThread.getString(4),rsThread.getString(5)),
						replies.toArray(new StoredTweet[replies.size()])));
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return threads.toArray(new TweetThread[threads.size()]);
	}
	
	public static void store(String screenName,Status tweet)
	{
		try
		{
			long rootID = tweet.getInReplyToStatusId();
			if(rootID != -1)
			{
				PreparedStatement getThreadStmt = getThreadByIDStmts.get(screenName);
				getThreadStmt.setLong(1,rootID);
				ResultSet rs = getThreadStmt.executeQuery();
				if(rs.next())
					rootID = rs.getLong(1);
				else
				{
					rs.close();
					PreparedStatement getReplyStmt = getReplyByIDStmts.get(screenName);
					getReplyStmt.setLong(1,rootID);
					rs = getReplyStmt.executeQuery();
					if(rs.next())
						rootID = rs.getLong(6);
					else
						rootID = -1;
				}
				rs.close();
			}

			Timestamp timestamp = new Timestamp(tweet.getCreatedAt().getTime());
			if(rootID != -1)
			{
				PreparedStatement storeReplyStmt = storeReplyStmts.get(screenName);
				
				storeReplyStmt.setLong(1,tweet.getId());
				storeReplyStmt.setTimestamp(2,timestamp);
				storeReplyStmt.setString(3,tweet.getText());
				storeReplyStmt.setString(4,tweet.getSource()
						.replaceAll("\\<\\/a\\>","")
						.replaceAll("\\<a.*\\>",""));
				storeReplyStmt.setString(5,tweet.getUser().getScreenName());
				storeReplyStmt.setLong(6,rootID);
				storeReplyStmt.execute();

				PreparedStatement lastUpdatedStmt = lastUpdatedStmts.get(screenName);
				lastUpdatedStmt.setTimestamp(1,timestamp);
				lastUpdatedStmt.setLong(2,rootID);
				lastUpdatedStmt.execute();
			}
			else
			{
				PreparedStatement storeThreadStmt = storeThreadStmts.get(screenName);
				
				storeThreadStmt.setLong(1,tweet.getId());
				storeThreadStmt.setTimestamp(2,timestamp);
				storeThreadStmt.setString(3,tweet.getText());
				storeThreadStmt.setString(4,tweet.getSource()
						.replaceAll("\\<\\/a\\>","")
						.replaceAll("\\<a.*\\>",""));
				storeThreadStmt.setString(5,tweet.getUser().getScreenName());
				storeThreadStmt.setShort(6,DATA_UNREAD);
				storeThreadStmt.setTimestamp(7,timestamp);
				storeThreadStmt.execute();
			}
		}
		catch(SQLException e)
		{
			System.out.println(e.getErrorCode());
			System.out.println(e.getSQLState());
			e.printStackTrace();
		}
		catch(Exception e)
		{	
			e.printStackTrace();
		}
	}
	
	public static void store(String user,List<Status> tweets)
	{
		for(int i = tweets.size() - 1; i >= 0; i--)
			store(user,tweets.get(i));
	}
	/*
	public static void main(String[] args)
	{
		try
		{
			Status status = new Status(
					new JSONStringer()
						.object()
							.key("id").value(239465243)
							.key("user").object()
								.key("id").value(93485)
								.key("name").value("andrew brown")
								.key("screen_name").value("dosfx")
								.key("location").value("The couch")
								.key("description").value("I hate this")
								.key("profile_image_url").value("out there in the internets")
								.key("url").value("ugh")
								.key("protected").value(true)
								.key("followers_count").value(10)
								.key("profile_background_color").value("balck")
								.key("profile_text_color").value("black")
								.key("profile_link_color").value("black")
								.key("profile_sidebar_fill_color").value("black")
								.key("profile_sidebar_border_color").value("black")
								.key("friends_count").value(10)
								.key("created_at").value("Tue Apr 07 22:52:51 +0000 2009")
								.key("favourites_count").value(10)
								.key("utc_offset").value(1)
								.key("time_zone").value("EST")
								.key("profile_background_image_url").value("I hate you")
								.key("profile_background_tile").value(false)
								.key("following").value(false)
								.key("notifications").value(false)
								.key("statuses_count").value(10)
								.endObject()
							.key("text").value("a tweet")
							.key("source").value("ooboo")
							.key("created_at").value("Tue Apr 07 22:52:51 +0000 2009")
							.key("in_reply_to_status_id").value(3453453)
							.key("in_reply_to_user_id").value(432857)
							.key("favorited").value(false)
						.endObject()
					.toString());
			System.out.print("Connecting... ");
			connect(false);
			System.out.println("Done.");
			System.out.println(getMaxID("DosFX"));
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}//*/
}
